﻿using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;//MySql命名空间
using System.Reflection;

public class MysqlManager
{
    string ip = "127.0.0.1";//本机ip地址
    string username = "root";
    string password = "123456";

    string dbName = "qikuedu";

    MySqlConnection connect;
    MySqlCommand command;
    MySqlDataReader reader;

    private static object lockObj = new object();

    private static MysqlManager _instance;
    public static MysqlManager Instance
    {
        get
        {
            lock(lockObj)
            {
                if (_instance == null)
                {
                    _instance = new MysqlManager();
                }
                return _instance;
            }
        }
    }

    public MysqlManager()
    {
        Open();
    }

    public void Open()
    {
        connect = new MySqlConnection();
        connect.ConnectionString = "server=" + ip + "; user id=" + username + "; password=" + password + "; database=" + dbName;
        connect.Open();
        Console.WriteLine("打开数据库成功");

        command = connect.CreateCommand();
    }

    public void Close()
    {
        command.Dispose();
        connect.Dispose();
        reader.Close();

        Console.WriteLine("关闭数据库成功");
    }

    public void CreateTable<T>() where T:class
    {
        Type type = typeof(T);
        string tableName = type.Name;
        FieldInfo[] fileds = type.GetFields();//通过反射获取type类的字段

        //FieldInfo.Name:字段名
        //FieldInfo.FieldType:字段类型
        string sql = "create table " + tableName + " (";
        foreach (var item in fileds)
        {
            if(item.Name=="id")
                sql += item.Name + " " + CS2DB(item.FieldType) + " primary key auto_increment,";
            else
                sql += item.Name + " " + CS2DB(item.FieldType) + ",";
        }
        sql = sql.TrimEnd(',')+")";
        ExcuteSql(sql);
    }

    string CS2DB(Type type)
    {
        string dbType = "";
        if (type.Name == "String")
        {
            dbType = "Text";
        }
        else if (type.Name == "Int32")
        {
            dbType = "Int";
        }
        else if (type.Name == "Single")
        {
            dbType = "Float";
        }
        return dbType;
    }

    //创建表
    void CreateTable(string tabelName, Dictionary<string, string> infos)
    {
        string sql = "create table" + " " + tabelName + "(";
        foreach (var item in infos)
        {
            sql += item.Key + " " + item.Value + ",";
        }
        sql = sql.TrimEnd(',') + ");";

        ExcuteSql(sql);
    }


    //增
    void InsertInto(string tableName)
    {
        string sql = "insert into " + tableName + " " + "values(1,'暴风雪',10,10)";
        ExcuteSql(sql);
    }

    public int InsertInto<T>(T t) where T:class
    {
        Type type = typeof(T);
        string tableName = type.Name;
        FieldInfo[] fileds = type.GetFields();

        string sql = "insert into " + tableName + " values(";
        foreach (FieldInfo item in fileds)
        {
            string value = item.GetValue(t).ToString();//通过反射得到t对象中某个字段的值
            sql += "'"+value + "' ,";
        }
        sql = sql.TrimEnd(',') + ")";
        return ExcuteSql(sql);
    }

    //删
    void DeleteFrom(string tableName)
    {
        string sql = "delete from " + tableName + " " + "where id = 1";
        ExcuteSql(sql);
    }
    public void DeleteFrom<T>(int id)
    {
        string tableName = typeof(T).Name;
        string sql = "delete from " + tableName + " " + "where id = " + id;
        ExcuteSql(sql);
    }

    //改
    void UpdateSet(string tableName)
    {
        string sql = "update " + tableName + " " + "set name = '无间炼狱' where id=1";
        ExcuteSql(sql);
    }

    public void UpdateSet<T>(T t)
    {
        Type type = typeof(T);
        string tableName = type.Name;

        FieldInfo[] fields = type.GetFields();
        int id = int.Parse(type.GetField("id").GetValue(t).ToString());

        string sql = "update " + tableName + " " + "set ";

        foreach (FieldInfo item in fields)
        {
            if(item.Name!="id")
            {
                sql += item.Name + " =  '"+ item.GetValue(t)+"' ,";
            }
        }
        sql = sql.TrimEnd(',');
        sql += " where id=" + id;

        int count = ExcuteSql(sql);
        if(count>0)
        {
            Console.WriteLine("修改成功：" + count + "行");
        }
    }

    //查
    void SelectFrom(string tableName)
    {
        string sql = "select * from " + tableName;

        reader = QuirySql(sql);//以阅读器来查询

        while (reader.Read())
        {
            int count = reader.FieldCount;
            for (int i = 0; i < count; i++)
            {
                string key = reader.GetName(i);//列名
                object value = reader.GetValue(i);//列值

                Console.WriteLine(key + ":" + value);
            }
        }
    }

    public T SelectByUserName<T>(string username) where T: class,new()
    {
        Type type = typeof(T);
        string tableName = type.Name;
        string sql = "select * from " + tableName + " where username = '" + username+"'";

        using (reader = QuirySql(sql)) //以阅读器来查询
        {
            if (reader.Read())//读取一次
            {
                T t = Activator.CreateInstance<T>();
                //T t = new T();
                int count = reader.FieldCount;
                for (int i = 0; i < count; i++)
                {
                    string key = reader.GetName(i);//列名
                    object value = reader.GetValue(i);//列值

                    type.GetField(key).SetValue(t, value);//把t中的key字段，填充数据value
                    Console.WriteLine(key + ":" + value);
                }
                return t;
            }
        }
        return null;
    }

    public List<T> SelectFrom<T>() where T : class, new()
    {
        Type type = typeof(T);
        string tableName = type.Name;
        string sql = "select * from " + tableName;

        List<T> array = new List<T>();

        using (reader = QuirySql(sql)) //以阅读器来查询
        {
            while (reader.Read())
            {
                T t = new T();
                int count = reader.FieldCount;
                for (int i = 0; i < count; i++)
                {
                    string key = reader.GetName(i);//列名
                    object value = reader.GetValue(i);//列值

                    //type.GetField("username").SetValue(t, "123456@qq.com");
                    type.GetField(key).SetValue(t, value);//把t中的key字段，填充数据value
                    Console.WriteLine(key + ":" + value);
                }
                array.Add(t);
            }

        }
        return array;
    }

    int ExcuteSql(string sql)
    {
        Console.WriteLine("sql -> " + sql);
        command.CommandText = sql;
        int effectCount= command.ExecuteNonQuery();
        return effectCount;
    }


    MySqlDataReader QuirySql(string sql)
    {
        Console.WriteLine("sql->"+sql);
        command.CommandText = sql;

        return command.ExecuteReader();
    }
}
